''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：pymysql_model.py
#日期：2018-10-22
#备注：pip install pymysql  pymysql是Python中操作MySQL的模块  
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import pymysql
import time
import xlrd
from xlutils.copy import copy
import os

start_time = time.time()  #计算程序运行时间

db = pymysql.connect(
    host="localhost",
    user="root",
    password="123456",
    port=3306,
    use_unicode=True,
    charset="utf8",
    database="sunshine")

cursor = db.cursor()

def createTable():
    sql1 = 'drop table if exists save_amount;'
    sql2 = 'create table save_amount(No INT(11) NOT NULL AUTO_INCREMENT,billNo VARCHAR(50),amount FLOAT DEFAULT 0.0,PRIMARY KEY (No));'
    cursor.execute(sql1)
    cursor.execute(sql2)
    db.commit()

def deleteTable():
    sql1 = 'delete from save_amount_bak;'
    cursor.execute(sql1)
    db.commit()


def data_to_mysql(billNo,amount):
    sql = "insert into save_amount(billNo,amount) values('%s','%f')" % (billNo,amount)
    try:
        # 使用 cursor() 方法创建一个游标对象 cursor
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        print('事务处理成功')


# 将有重复运单号和对账金额为0的异常数据拿出来
def get_error_data():
    cursor.execute('select * from save_amount where billNo in (select billNo from save_amount group by billNo having count(billNo)>1);')
    cursor.execute("select * from save_amount where No in (select No from save_amount where amount =0 and SUBSTRING(billNo,1,3) in ('218','518','618','818'));")
    results = cursor.fetchall()
    return results


# 删除数据库表中重复的数据，（删除前将重复的数据备份到另外一个表中）
def deal_mysql_data():
    sql5 = 'delete from save_amount where No in (select No from (select No from save_amount where billNo in (select billNo from save_amount group by billNo having count(billNo)>1) and No not in (select min(No) from save_amount group by billNo having count(billNo)>1)) as temp);'
    try:
        cursor.execute(sql5)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        print('事务处理成功')



# 将根据对账单Excel中数据库查找的信息copy到一个新的excel中(不能循环)
def mysql_to_excel(xlsFile,newFile):
    a = 1
    data = xlrd.open_workbook(xlsFile, formatting_info=True)
    table = data.sheets()[0]
    nrows = table.nrows
    new_excel = copy(data)
    ws = new_excel.get_sheet(0)  # 获取第一个sheet

    for i in range(1, nrows):
        billNo = table.cell_value(i, 1)
        cursor.execute('select billNo,amount from save_amount where billNo=%s', (billNo))
        results = cursor.fetchall()  # 获取多条数据

        # row_1 = cursor.fetchone()   # 获取一条数据
        for i in range(len(results)):
            amount = results[i][1]
            print(a+1,billNo,amount)
            ws.write(a, 11, billNo)    # 12列L
            ws.write(a, 12, amount)    # 13列M
            a = a + 1
    new_excel.save(newFile)



''''''''
def main():
    createTable()        # 创建一个空白数据库表
    deleteTable()        # 删除备份表的数据


if __name__ == '__main__':
    main()
    endtime = time.time() - start_time
    print('程序运行了%.2f秒' % endtime)